﻿if exists (select 1
            from  sysobjects
           where  id = object_id('AppUserInfo')
            and   type = 'U')
   drop table AppUserInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('OperateLog')
            and   type = 'U')
   drop table OperateLog
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RequestMessageLog')
            and   type = 'U')
   drop table RequestMessageLog
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ResponseMessageLog')
            and   type = 'U')
   drop table ResponseMessageLog
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SysConfig')
            and   type = 'U')
   drop table SysConfig
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Users')
            and   type = 'U')
   drop table Users
go

/*==============================================================*/
/* Table: AppUserInfo                                           */
/*==============================================================*/
create table AppUserInfo (
   id                   int                  identity,
   appid                varchar(100)         not null,
   openid               varchar(100)         not null,
   nickname             varchar(100)         null,
   sex                  int                  null,
   language             varchar(20)          null,
   city                 nvarchar(100)        null,
   province             nvarchar(20)         null,
   country              nvarchar(20)         null,
   headimgurl           varchar(max)         null,
   subscribe            int                  not null default 0,
   subscribe_time       bigint               null,
   createtime           datetime             null default getdate(),
   constraint PK_APPUSERINFO primary key (id)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sys.sp_addextendedproperty 'MS_Description', 
   '关注用户信息',
   'schema', @CurrentUser, 'table', 'AppUserInfo'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '应用ID',
   'schema', @CurrentUser, 'table', 'AppUserInfo', 'column', 'appid'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '普通用户的标识，对当前公众号唯一',
   'schema', @CurrentUser, 'table', 'AppUserInfo', 'column', 'openid'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户的昵称',
   'schema', @CurrentUser, 'table', 'AppUserInfo', 'column', 'nickname'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户的性别，值为1时是男性，值为2时是女性，值为0时是未知',
   'schema', @CurrentUser, 'table', 'AppUserInfo', 'column', 'sex'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户的语言，简体中文为zh_CN',
   'schema', @CurrentUser, 'table', 'AppUserInfo', 'column', 'language'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '城市',
   'schema', @CurrentUser, 'table', 'AppUserInfo', 'column', 'city'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '省',
   'schema', @CurrentUser, 'table', 'AppUserInfo', 'column', 'province'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '国家',
   'schema', @CurrentUser, 'table', 'AppUserInfo', 'column', 'country'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户头像，最后一个数值代表正方形头像大小（有0、46、64、96、132数值可选，0代表640*640正方形头像），用户没有头像时该项为空',
   'schema', @CurrentUser, 'table', 'AppUserInfo', 'column', 'headimgurl'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户是否订阅该公众号标识，值为0时，代表此用户没有关注该公众号，拉取不到其余信息',
   'schema', @CurrentUser, 'table', 'AppUserInfo', 'column', 'subscribe'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户关注时间，为时间戳。如果用户曾多次关注，则取最后关注时间',
   'schema', @CurrentUser, 'table', 'AppUserInfo', 'column', 'subscribe_time'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '导入时间',
   'schema', @CurrentUser, 'table', 'AppUserInfo', 'column', 'createtime'
go

/*==============================================================*/
/* Table: OperateLog                                            */
/*==============================================================*/
create table OperateLog (
   LogId                int                  identity,
   UserId               varchar(20)          null,
   ClientIp             varchar(50)          null,
   LogType              tinyint              null default 1,
   Content              nvarchar(Max)        null,
   LogDate              datetime             null default getdate(),
   constraint PK_OPERATELOG primary key (LogId)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sys.sp_addextendedproperty 'MS_Description', 
   '操作日志',
   'schema', @CurrentUser, 'table', 'OperateLog'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '日志ID',
   'schema', @CurrentUser, 'table', 'OperateLog', 'column', 'LogId'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '操作用户',
   'schema', @CurrentUser, 'table', 'OperateLog', 'column', 'UserId'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '客户端IP',
   'schema', @CurrentUser, 'table', 'OperateLog', 'column', 'ClientIp'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '日志类型
   Login-1-登录
   Insert-2-添加
   Delete-3-删除
   Edit-4-修改
   Other-5-其他
   ',
   'schema', @CurrentUser, 'table', 'OperateLog', 'column', 'LogType'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '操作内容',
   'schema', @CurrentUser, 'table', 'OperateLog', 'column', 'Content'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '日志时间',
   'schema', @CurrentUser, 'table', 'OperateLog', 'column', 'LogDate'
go

/*==============================================================*/
/* Table: RequestMessageLog                                     */
/*==============================================================*/
create table RequestMessageLog (
   ReqId                bigint               identity,
   MsgId                bigint               null,
   MsgType              varchar(20)          null,
   ToUser               varchar(100)         null,
   FromUser             varchar(100)         null,
   CreateTime           datetime             null,
   ReqXml               nvarchar(max)        null,
   ReqTime              datetime             null default getdate(),
   constraint PK_REQUESTMESSAGELOG primary key (ReqId)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sys.sp_addextendedproperty 'MS_Description', 
   '请求消息日志表',
   'schema', @CurrentUser, 'table', 'RequestMessageLog'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '日志ID',
   'schema', @CurrentUser, 'table', 'RequestMessageLog', 'column', 'ReqId'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '请求消息ID',
   'schema', @CurrentUser, 'table', 'RequestMessageLog', 'column', 'MsgId'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '消息类型',
   'schema', @CurrentUser, 'table', 'RequestMessageLog', 'column', 'MsgType'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '接收用户',
   'schema', @CurrentUser, 'table', 'RequestMessageLog', 'column', 'ToUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '来源用户',
   'schema', @CurrentUser, 'table', 'RequestMessageLog', 'column', 'FromUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建时间',
   'schema', @CurrentUser, 'table', 'RequestMessageLog', 'column', 'CreateTime'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '请求文本',
   'schema', @CurrentUser, 'table', 'RequestMessageLog', 'column', 'ReqXml'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '请求时间',
   'schema', @CurrentUser, 'table', 'RequestMessageLog', 'column', 'ReqTime'
go

/*==============================================================*/
/* Table: ResponseMessageLog                                    */
/*==============================================================*/
create table ResponseMessageLog (
   RespId               bigint               identity,
   MsgType              varchar(20)          null,
   ToUser               varchar(100)         null,
   FromUser             varchar(100)         null,
   CreateTime           datetime             null,
   RespXml              nvarchar(max)        null,
   RespTime             datetime             null default getdate(),
   constraint PK_RESPONSEMESSAGELOG primary key (RespId)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sys.sp_addextendedproperty 'MS_Description', 
   '响应消息日志',
   'schema', @CurrentUser, 'table', 'ResponseMessageLog'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '日志ID',
   'schema', @CurrentUser, 'table', 'ResponseMessageLog', 'column', 'RespId'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '消息类型',
   'schema', @CurrentUser, 'table', 'ResponseMessageLog', 'column', 'MsgType'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '接收用户',
   'schema', @CurrentUser, 'table', 'ResponseMessageLog', 'column', 'ToUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '来源用户',
   'schema', @CurrentUser, 'table', 'ResponseMessageLog', 'column', 'FromUser'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建时间',
   'schema', @CurrentUser, 'table', 'ResponseMessageLog', 'column', 'CreateTime'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '响应文本',
   'schema', @CurrentUser, 'table', 'ResponseMessageLog', 'column', 'RespXml'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '响应文本',
   'schema', @CurrentUser, 'table', 'ResponseMessageLog', 'column', 'RespTime'
go

/*==============================================================*/
/* Table: SysConfig                                             */
/*==============================================================*/
create table SysConfig (
   SysKey               varchar(100)         not null,
   SysValue             nvarchar(max)        null,
   SysType              tinyint              not null,
   constraint PK_SYSCONFIG primary key (SysKey)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sys.sp_addextendedproperty 'MS_Description', 
   '系统参数配置',
   'schema', @CurrentUser, 'table', 'SysConfig'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '参数名称',
   'schema', @CurrentUser, 'table', 'SysConfig', 'column', 'SysKey'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '参数值',
   'schema', @CurrentUser, 'table', 'SysConfig', 'column', 'SysValue'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '参数类型
   Sys-1-系统参数',
   'schema', @CurrentUser, 'table', 'SysConfig', 'column', 'SysType'
go

/*==============================================================*/
/* Table: Users                                                 */
/*==============================================================*/
create table Users (
   UserId               varchar(20)          not null,
   Password             varchar(100)         not null,
   NickName             nvarchar(50)         null,
   Phone                varchar(20)          null,
   Email                varchar(100)         null,
   RecTime              datetime             not null default getdate(),
   LoginTime            datetime             not null default getdate(),
   constraint PK_USERS primary key (UserId)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sys.sp_addextendedproperty 'MS_Description', 
   '登录用户',
   'schema', @CurrentUser, 'table', 'Users'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户名',
   'schema', @CurrentUser, 'table', 'Users', 'column', 'UserId'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '密码',
   'schema', @CurrentUser, 'table', 'Users', 'column', 'Password'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '昵称',
   'schema', @CurrentUser, 'table', 'Users', 'column', 'NickName'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '电话',
   'schema', @CurrentUser, 'table', 'Users', 'column', 'Phone'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '邮箱地址',
   'schema', @CurrentUser, 'table', 'Users', 'column', 'Email'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建时间',
   'schema', @CurrentUser, 'table', 'Users', 'column', 'RecTime'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '最后一次登录时间',
   'schema', @CurrentUser, 'table', 'Users', 'column', 'LoginTime'
go
